import os
from flask import (
    render_template,
    request,
    Response,
    flash,
    redirect,
    url_for,
    jsonify,
    g,
    send_from_directory,
    Blueprint,
)
from models import (
    app,
    User,
    Jobnumber,
    Category,
    Subcategory,
    Task,
    Assignment,
    db,
    Holiday,
    Attachment,
    DepartmentOrg,
    ProjectOrg,
    ProjectGrade,
    DepartmentGrade,
)
from flask_login import login_required, current_user
from bp_admin import admin_required
from bp_plot import get_category_timesheet_by_project, get_user_timesheet_by_project
from datetime import date, timedelta

from fun_user_workhours import (
    GetMonthlyList,
    GetMonthlyListSummary,
    GetUserOffworkDetail,
)
from fun_workdays import GetStandWorkdays

from sqlalchemy import func, Integer
from sqlalchemy.sql.expression import cast


bp_assess = Blueprint("bp_assess", __name__)


@bp_assess.route("/", methods=["GET"])
@login_required
def index():
    return redirect(url_for("bp_assess.list_department_org"), code=302)


@bp_assess.route("/score", methods=["GET"])
@login_required
def score():
    today = date.today()
    year = today.year
    month = today.month
    return redirect(url_for("bp_assess.score_users", year=year, month=month), code=302)


@bp_assess.route("/score_list", methods=["GET"])
@login_required
def score_list_this_month():
    today = date.today()
    year = today.year
    month = today.month
    return redirect(url_for("bp_assess.score_list", year=year, month=month), code=302)


@bp_assess.route("/<int:year>/<int:month>/list_all", methods=["GET"])
@login_required
def score_list(year, month):
    if not (year > 2000 and year < 2050):
        flash("年份超出范围,跳转到当月", category="danger")
        return redirect(url_for("bp_assess.score_list_this_month"), code=302)

    if not (month >= 1 and month <= 12):
        flash("月份超出范围,跳转到当月", category="danger")
        return redirect(url_for("bp_assess.score_list_this_month"), code=302)

    # project_average_grade = (
    #     db.session.query(
    #         ProjectGrade.user_id, func.avg(ProjectGrade.grade).label("avg"),
    #     )
    #     .filter(ProjectGrade.delete == False)
    #     .filter(ProjectGrade.year == year)
    #     .filter(ProjectGrade.month == month)
    #     .group_by(ProjectGrade.user_id)
    #     .subquery()
    # )

    # users = (
    #     db.session.query(
    #         User.id,
    #         User.no,
    #         User.name,
    #         User.position,
    #         DepartmentGrade.grade,
    #         project_average_grade.c.avg,
    #     )
    #     .filter(~User.id.in_((1, 4, 7, 116, 117, 118)))
    #     .filter(User.delete == False)
    #     .join(
    #         DepartmentGrade,
    #         (DepartmentGrade.year == year)
    #         & (DepartmentGrade.month == month)
    #         & (DepartmentGrade.delete == False)
    #         & (DepartmentGrade.user_id == User.id),
    #         isouter=True,
    #     )
    #     .join(
    #         project_average_grade,
    #         project_average_grade.c.user_id == User.id,
    #         isouter=True,
    #     )
    # )
    stdworkdays = GetStandWorkdays(year, month)
    fields, users = GetMonthlyListSummary(year, month)
    return render_template(
        "assess/score_list.html",
        users=users,
        month=month,
        year=year,
        stdworkdays=stdworkdays,
        fields=fields,
    )


@bp_assess.route(
    "/<int:year>/<int:month>/user_project_grade/<int:user_id>", methods=["GET"]
)
@login_required
def user_project_grade(year, month, user_id):
    if not (year > 2000 and year < 2050):
        flash("年份超出范围", category="danger")
        return redirect(url_for("bp_assess.score_list_this_month"), code=302)

    if not (month >= 1 and month <= 12):
        flash("月份超出范围", category="danger")
        return redirect(url_for("bp_assess.score_list_this_month"), code=302)

    user = User.query.filter_by(id=user_id).first()
    if not user:
        flash("用户不存在", category="danger")
        return redirect(url_for("bp_assess.score_list_this_month"), code=302)

    username = user.name
    grades = (
        db.session.query(ProjectGrade.grade, Jobnumber.name, Jobnumber.desc, User.name)
        .filter(ProjectGrade.delete == False)
        .filter(ProjectGrade.user_id == user_id)
        .filter(ProjectGrade.year == year)
        .filter(ProjectGrade.month == month)
        .join(Jobnumber, Jobnumber.id == ProjectGrade.project_id)
        .join(User, User.id == ProjectGrade.assign_user_id)
    )
    return render_template(
        "assess/user_project_grade.html",
        grades=grades,
        month=month,
        year=year,
        username=username,
    )


@bp_assess.route(
    "/<int:year>/<int:month>/user_offwork_detail/<int:user_id>", methods=["GET"]
)
@login_required
def user_offwork_detail(year, month, user_id):
    if not (year > 2000 and year < 2050):
        flash("年份超出范围", category="danger")
        return redirect(url_for("bp_assess.score_list_this_month"), code=302)

    if not (month >= 1 and month <= 12):
        flash("月份超出范围", category="danger")
        return redirect(url_for("bp_assess.score_list_this_month"), code=302)

    user = User.query.filter_by(id=user_id).first()
    if not user:
        flash("用户不存在", category="danger")
        return redirect(url_for("bp_assess.score_list_this_month"), code=302)

    username = user.name
    offwork_list = GetUserOffworkDetail(year, month, user_id)
    return render_template(
        "assess/user_offwork_detail.html",
        offwork_list=offwork_list,
        month=month,
        year=year,
        username=username,
    )


@bp_assess.route(
    "/set_plan_manhour/<int:project_org_id>/<int:plan_manhour>/<int:project_id>",
    methods=["GET"],
)
@login_required
@admin_required
def set_plan_manhour(project_org_id, plan_manhour, project_id):
    project_org = ProjectOrg.query.filter_by(id=project_org_id).first()
    project_org.plan_manhour = plan_manhour
    db.session.commit()

    return redirect(
        url_for("bp_assess.list_project_org", project_id=project_id), code=302
    )


@bp_assess.route(
    "/<int:year>/<int:month>/score_user/<int:user_id>/<int:grade>", methods=["GET"]
)
@login_required
def score_user(year, month, user_id, grade):
    if not (year > 2000 and year < 2050):
        flash("年份超出范围,跳转到当月", category="danger")
        return redirect(url_for("bp_assess.score"), code=302)

    if not (month >= 1 and month <= 12):
        flash("月份超出范围,跳转到当月", category="danger")
        return redirect(url_for("bp_assess.score"), code=302)

    if not (grade > 0 and grade <= 100):
        flash("分数只能是 0 - 100", category="danger")
        return redirect(url_for("bp_assess.score"), code=302)

    exist_grade = DepartmentGrade.query.filter_by(
        year=year, month=month, user_id=user_id, delete=False
    ).first()

    if exist_grade:
        exist_grade.grade = grade
        exist_grade.assign_user_id = current_user.id
        db.session.commit()

    else:
        new_grade = DepartmentGrade(
            year=year,
            month=month,
            user_id=user_id,
            grade=grade,
            assign_user_id=current_user.id,
        )
        db.session.add(new_grade)
        db.session.commit()

    return redirect(url_for("bp_assess.score"), code=302)


@bp_assess.route(
    "/<int:year>/<int:month>/score_user/<int:project_id>/<int:user_id>/<int:grade>",
    methods=["GET"],
)
@login_required
def score_project_user(year, month, project_id, user_id, grade):
    if not (year > 2000 and year < 2050):
        flash("年份超出范围,跳转到当月", category="danger")
        return redirect(url_for("bp_assess.score"), code=302)

    if not (month >= 1 and month <= 12):
        flash("月份超出范围,跳转到当月", category="danger")
        return redirect(url_for("bp_assess.score"), code=302)

    if not (grade > 0 and grade <= 100):
        flash("分数只能是 0 - 100", category="danger")
        return redirect(url_for("bp_assess.score"), code=302)

    exist_grade = ProjectGrade.query.filter_by(
        year=year, month=month, user_id=user_id, project_id=project_id, delete=False
    ).first()

    if exist_grade:
        exist_grade.grade = grade
        exist_grade.assign_user_id = current_user.id
        db.session.commit()

    else:
        new_grade = ProjectGrade(
            year=year,
            month=month,
            project_id=project_id,
            user_id=user_id,
            grade=grade,
            assign_user_id=current_user.id,
        )
        db.session.add(new_grade)
        db.session.commit()

    return redirect(url_for("bp_assess.score"), code=302)


@bp_assess.route("/<int:year>/<int:month>/score_users", methods=["GET"])
@login_required
def score_users(year, month):
    if not (year > 2000 and year < 2050):
        flash("年份超出范围,跳转到当月", category="danger")
        return redirect(url_for("bp_assess.score"), code=302)

    if not (month >= 1 and month <= 12):
        flash("月份超出范围,跳转到当月", category="danger")
        return redirect(url_for("bp_assess.score"), code=302)

    ## 部门组织架构 为了显示组织架构图 使用了过渡用户 116 117 118
    ## 可维护性很差，需要将来优化 测试 4747 6016

    org_mem = (
        db.session.query(DepartmentOrg.child_id)
        .filter(DepartmentOrg.parent_id == current_user.id)
        .all()
    )
    children = [x for (x,) in org_mem]

    has_special_id = set([116, 117, 118]).intersection(children)

    if has_special_id:
        special_id = has_special_id.pop()
        special_org_mem = (
            db.session.query(DepartmentOrg.child_id)
            .filter(DepartmentOrg.parent_id == special_id)
            .all()
        )
        special_children = [x for (x,) in special_org_mem]
        children.remove(special_id)
        children = children + special_children

    org_children = (
        db.session.query(
            User.id, User.no, User.name, User.position, DepartmentGrade.grade
        )
        .filter(User.delete == False)
        .filter(User.id.in_(children))
        .join(
            DepartmentGrade,
            (DepartmentGrade.year == year)
            & (DepartmentGrade.month == month)
            & (DepartmentGrade.delete == False)
            & (DepartmentGrade.user_id == User.id),
            isouter=True,
        )
        .all()
    )

    ## 项目打分
    project_ids_in_charge = [
        x
        for (x,) in (
            db.session.query(ProjectOrg.project_id)
            .filter(ProjectOrg.user_id == current_user.id)
            .filter(ProjectOrg.is_manager == True)
            .all()
        )
    ]

    project_user_list = (
        db.session.query(
            ProjectOrg.project_id,
            ProjectOrg.user_id,
            Jobnumber.name,
            Jobnumber.desc,
            User.no,
            User.name,
            ProjectGrade.grade,
        )
        .filter(ProjectOrg.project_id.in_(project_ids_in_charge))
        .filter(ProjectOrg.is_manager == False)
        .join(User, User.id == ProjectOrg.user_id)
        .join(Jobnumber, Jobnumber.id == ProjectOrg.project_id)
        .filter(User.delete == False)
        .filter(Jobnumber.delete == False)
        .join(
            ProjectGrade,
            (ProjectGrade.year == year)
            & (ProjectGrade.month == month)
            & (ProjectGrade.delete == False)
            & (ProjectGrade.user_id == ProjectOrg.user_id)
            & (ProjectGrade.project_id == ProjectOrg.project_id),
            isouter=True,
        )
        .all()
    )

    ## 设计经理考核
    if current_user.id in (2, 7, 10):
        design_manager_list = (
            db.session.query(
                ProjectOrg.project_id,
                ProjectOrg.user_id,
                Jobnumber.name,
                Jobnumber.desc,
                User.no,
                User.name,
                ProjectGrade.grade,
            )
            .filter(ProjectOrg.is_manager == True)
            .join(User, User.id == ProjectOrg.user_id)
            .join(Jobnumber, Jobnumber.id == ProjectOrg.project_id)
            .filter(User.delete == False)
            .filter(Jobnumber.delete == False)
            .join(
                ProjectGrade,
                (ProjectGrade.year == year)
                & (ProjectGrade.month == month)
                & (ProjectGrade.delete == False)
                & (ProjectGrade.user_id == ProjectOrg.user_id)
                & (ProjectGrade.project_id == ProjectOrg.project_id),
                isouter=True,
            )
            .all()
        )
    else:
        design_manager_list = None

    if not project_user_list:
        flash("如果你是设计经理，但是没有项目信息，请联系金曼添加。", category="success")
    flash("如果项目已结束，请联系金曼关闭。", category="info")

    return render_template(
        "assess/score_users.html",
        org_children=org_children,
        project_user_list=project_user_list,
        design_manager_list=design_manager_list,
        year=year,
        month=month,
    )


@bp_assess.route("/list_users", methods=["GET"])
@login_required
def list_users():

    today = date.today()
    year = today.year
    month = today.month

    this_month_start_date = today.replace(day=1)
    last_month_end_date = this_month_start_date
    if month == 12:
        next_month = 1
        this_month_end_date = today.replace(day=1, month=next_month, year=year + 1)
    else:
        next_month = month + 1
        this_month_end_date = today.replace(day=1, month=next_month, year=year)

    last_month_start_date = (last_month_end_date - timedelta(days=1)).replace(day=1)

    user_hour_sum_this_month = (
        db.session.query(Task.userid, func.sum(Task.manhour).label("summ"))
        .filter_by(delete=False)
        .filter(Task.categoryid != 33)
        .filter(func.DATE(Task.tasktime) >= this_month_start_date)
        .filter(func.DATE(Task.tasktime) < this_month_end_date)
        .group_by(Task.userid)
        .subquery()
    )

    user_hour_sum_last_month = (
        db.session.query(Task.userid, func.sum(Task.manhour).label("sumlm"))
        .filter_by(delete=False)
        .filter(Task.categoryid != 33)
        .filter(func.DATE(Task.tasktime) >= last_month_start_date)
        .filter(func.DATE(Task.tasktime) < last_month_end_date)
        .group_by(Task.userid)
        .subquery()
    )

    user_hour_sum_today = (
        db.session.query(Task.userid, func.sum(Task.manhour).label("sumd"))
        .filter_by(delete=False)
        .filter(Task.categoryid != 33)
        .filter(func.DATE(Task.tasktime) == date.today())
        .group_by(Task.userid)
        .subquery()
    )

    user_hour_sum_yesterday = (
        db.session.query(Task.userid, func.sum(Task.manhour).label("sumy"))
        .filter_by(delete=False)
        .filter(Task.categoryid != 33)
        .filter(func.DATE(Task.tasktime) == date.today() - timedelta(days=1))
        .group_by(Task.userid)
        .subquery()
    )

    users = (
        db.session.query(
            User.id,
            User.no,
            User.name,
            User.position,
            user_hour_sum_this_month.c.summ,
            user_hour_sum_last_month.c.sumlm,
            user_hour_sum_yesterday.c.sumy,
            user_hour_sum_today.c.sumd,
        )
        .filter(User.id != 1)
        .filter(User.id != 4)
        .filter(User.id != 7)
        .filter(User.id != 116)
        .filter(User.id != 117)
        .filter(User.id != 118)
        .outerjoin(
            user_hour_sum_this_month, user_hour_sum_this_month.c.userid == User.id,
        )
        .outerjoin(
            user_hour_sum_last_month, user_hour_sum_last_month.c.userid == User.id,
        )
        .outerjoin(
            user_hour_sum_yesterday, user_hour_sum_yesterday.c.userid == User.id,
        )
        .outerjoin(user_hour_sum_today, user_hour_sum_today.c.userid == User.id)
        .filter(User.delete == False)
    )
    return render_template("assess/users.html", users=users)


@bp_assess.route("/project_detail/<project_id>", methods=["GET"])
@login_required
def project_detail(project_id):
    project_name = (
        db.session.query(Jobnumber.name, Jobnumber.desc).filter_by(id=project_id).all()
    )

    if not project_name:
        flash("项目不存在", category="danger")
        return redirect(url_for("bp_assess.list_projects"), code=302)

    tasks = (
        db.session.query(
            Task.id,
            Task.title,
            Task.remark,
            User.no,
            User.name,
            Category.name,
            Subcategory.name,
            Task.manhour,
            Task.tasktime,
            Task.createtime,
        )
        .filter_by(delete=False)
        .filter_by(jobnumberid=project_id)
        .join(User, User.id == Task.userid)
        .join(Category, Category.id == Task.categoryid)
        .join(Subcategory, Subcategory.id == Task.subcategoryid)
    )

    category_data = get_category_timesheet_by_project(project_id)
    user_data = get_user_timesheet_by_project(project_id)

    return render_template(
        "assess/project_detail.html",
        tasks=tasks,
        project_name=project_name,
        category_data=category_data,
        user_data=user_data,
    )


@bp_assess.route("/list_projects", methods=["GET"])
@login_required
def list_projects():
    project_mem_count = (
        db.session.query(
            ProjectOrg.project_id, func.count(ProjectOrg.id).label("count")
        )
        .group_by(ProjectOrg.project_id)
        .subquery()
    )

    project_hour_sum = (
        db.session.query(Task.jobnumberid, func.sum(Task.manhour).label("sum"))
        .filter_by(delete=False)
        .group_by(Task.jobnumberid)
        .subquery()
    )

    # today = date.today() - timedelta(days=1)
    today = date.today()
    year = today.year
    month = today.month

    this_month_start_date = today.replace(day=1)
    last_month_end_date = this_month_start_date
    if month == 12:
        next_month = 1
        this_month_end_date = today.replace(day=1, month=next_month, year=year + 1)
    else:
        next_month = month + 1
        this_month_end_date = today.replace(day=1, month=next_month, year=year)

    last_month_start_date = (last_month_end_date - timedelta(days=1)).replace(day=1)

    project_hour_sum_this_month = (
        db.session.query(Task.jobnumberid, func.sum(Task.manhour).label("summ"))
        .filter_by(delete=False)
        .filter(func.DATE(Task.tasktime) >= this_month_start_date)
        .filter(func.DATE(Task.tasktime) < this_month_end_date)
        .group_by(Task.jobnumberid)
        .subquery()
    )

    project_hour_sum_last_month = (
        db.session.query(Task.jobnumberid, func.sum(Task.manhour).label("sumlm"))
        .filter_by(delete=False)
        .filter(func.DATE(Task.tasktime) >= last_month_start_date)
        .filter(func.DATE(Task.tasktime) < last_month_end_date)
        .group_by(Task.jobnumberid)
        .subquery()
    )

    project_hour_sum_today = (
        db.session.query(Task.jobnumberid, func.sum(Task.manhour).label("sumd"))
        .filter_by(delete=False)
        .filter(func.DATE(Task.tasktime) == date.today())
        .group_by(Task.jobnumberid)
        .subquery()
    )

    project_hour_sum_yesterday = (
        db.session.query(Task.jobnumberid, func.sum(Task.manhour).label("sumy"))
        .filter_by(delete=False)
        .filter(func.DATE(Task.tasktime) == date.today() - timedelta(days=1))
        .group_by(Task.jobnumberid)
        .subquery()
    )

    projects = (
        db.session.query(
            Jobnumber.id,
            Jobnumber.name,
            Jobnumber.desc,
            project_mem_count.c.count,
            project_hour_sum.c.sum,
            project_hour_sum_this_month.c.summ,
            project_hour_sum_last_month.c.sumlm,
            project_hour_sum_yesterday.c.sumy,
            project_hour_sum_today.c.sumd,
        )
        .outerjoin(project_mem_count, project_mem_count.c.project_id == Jobnumber.id)
        .outerjoin(project_hour_sum, project_hour_sum.c.jobnumberid == Jobnumber.id)
        .outerjoin(
            project_hour_sum_this_month,
            project_hour_sum_this_month.c.jobnumberid == Jobnumber.id,
        )
        .outerjoin(
            project_hour_sum_last_month,
            project_hour_sum_last_month.c.jobnumberid == Jobnumber.id,
        )
        .outerjoin(
            project_hour_sum_yesterday,
            project_hour_sum_yesterday.c.jobnumberid == Jobnumber.id,
        )
        .outerjoin(
            project_hour_sum_today, project_hour_sum_today.c.jobnumberid == Jobnumber.id
        )
        .filter(Jobnumber.delete == False)
    )

    flash("点击工作令号可查看项目成员", category="success")
    flash("点击项目名称可查看填报明细", category="danger")
    flash("组织架构维护请联系金曼", category="info")
    # flash("也支持正则搜索 如 搜索非休假事件 ^((?!休假).)*$ ", category="success")
    return render_template("assess/projects.html", projects=projects)


@bp_assess.route("/list_project_org/<project_id>", methods=["GET"])
@login_required
def list_project_org(project_id):
    project_name = (
        db.session.query(Jobnumber.name, Jobnumber.desc).filter_by(id=project_id).all()
    )

    if not project_name:
        flash("项目不存在", category="danger")
        return redirect(url_for("bp_assess.list_projects"), code=302)

    # project_users = (
    #     db.session.query(ProjectOrg.id, ProjectOrg.is_manager, User.name, ProjectOrg.plan_manhour, func.sum(Task.manhour).label('total'))
    #     .join(User, User.id == ProjectOrg.user_id)
    #     .join(Task, (Task.userid == ProjectOrg.user_id) & (Task.jobnumberid == ProjectOrg.project_id), isouter=True)
    #     .filter(ProjectOrg.project_id == project_id)
    #     .filter(User.delete == False)
    #     .filter(Task.delete == False)
    #     .group_by(ProjectOrg.id)
    #     .group_by(ProjectOrg.is_manager)
    #     .group_by(User.name)
    #     .group_by(ProjectOrg.plan_manhour)
    #     .group_by(Task.userid)
    #     .group_by(Task.jobnumberid)
    #     .all()
    # )
    # project_users = (
    #     db.session.query(ProjectOrg.id, ProjectOrg.is_manager, User.name, ProjectOrg.plan_manhour, ProjectOrg.user_id)
    #     .join(User, User.id == ProjectOrg.user_id)
    #     .filter(ProjectOrg.project_id == project_id)
    #     .filter(User.delete == False)
    #     .all()
    # )

    sql = """
        SELECT
        project_org.id as id,
        project_org.is_manager as is_mamanger,
        user.name as name,
        project_org.plan_manhour as plan_manhour,
        s.total,
        user.id
        FROM project_org
        LEFT JOIN user ON user.id = project_org.user_id
        LEFT JOIN
        (
            SELECT
            project_org.id as id,
            SUM(task.manhour) as total
            FROM project_org
            LEFT JOIN task
            ON project_org.project_id = task.jobnumberid AND project_org.user_id = task.userid
            WHERE project_org.project_id ={0} AND task.delete = FALSE
            GROUP BY project_org.id
        ) s ON project_org.id = s.id
        WHERE project_org.project_id = {0} and user.delete = false
    """.format(
        project_id
    )

    project_users = db.session.execute(sql).fetchall()

    if not project_users:
        flash("组织架构不存在", category="danger")

    admin_count = sum(1 for x in project_users if x[1] == True)

    if admin_count == 0:
        flash("项目负责人不存在", category="danger")
    if admin_count > 1:
        flash("存在多个项目负责人", category="warning")

    flash("计划工时请联系金曼维护。", category="success")
    return render_template(
        "assess/project_org.html",
        project_name=project_name[0],
        project_users=project_users,
        project_id=project_id,
    )


@bp_assess.route("/delete_project_org/<project_id>/<org_id>", methods=["GET"])
@login_required
@admin_required
def delete_project_org(project_id, org_id):
    org = ProjectOrg.query.filter_by(id=org_id).one()
    if org:
        db.session.delete(org)
        db.session.commit()

    return redirect(
        url_for("bp_assess.list_project_org", project_id=project_id), code=302
    )


@bp_assess.route("/modify_project_org/<project_id>/<org_id>", methods=["GET"])
@login_required
@admin_required
def modify_project_org(project_id, org_id):
    org = ProjectOrg.query.filter_by(id=org_id).first()
    if org:
        org.is_manager = not org.is_manager
        db.session.commit()

    return redirect(
        url_for("bp_assess.list_project_org", project_id=project_id), code=302
    )


@bp_assess.route("/add_project_org/<project_id>", methods=["GET", "POST"])
@login_required
@admin_required
def add_project_org(project_id):
    project_name = (
        db.session.query(Jobnumber.name, Jobnumber.desc).filter_by(id=project_id).all()
    )
    if not project_name:
        flash("项目不存在", category="danger")
        return redirect(url_for("bp_assess.list_projects"), code=302)

    if request.method == "POST":

        for user in request.form.getlist("projectuser"):
            db.session.add(ProjectOrg(project_id=project_id, user_id=user))
            db.session.commit()

        return redirect(
            url_for("bp_assess.list_project_org", project_id=project_id), code=302
        )
    else:
        all_users = (
            db.session.query(User.id, User.no, User.name, User.position)
            .filter_by(delete=False)
            .all()
        )
        project_users = (
            db.session.query(ProjectOrg.user_id).filter_by(project_id=project_id).all()
        )
        remove_users = [1, 116, 117, 118] + [x for (x,) in project_users]
        new_users = list(filter(lambda x: x[0] not in remove_users, all_users))

        new_users = sorted(new_users, key=lambda x: (x[3], x[1]))

        return render_template(
            "assess/add_project_org.html",
            project_name=project_name[0],
            project_id=project_id,
            users=new_users,
        )


@bp_assess.route("/department_org", methods=["GET"])
@login_required
def list_department_org():
    # orgs = db.session.query(DepartmentOrg.id,DepartmentOrg.parent_id, DepartmentOrg.child_id, User.name, User.position).join(User, User.id==DepartmentOrg.child_id).filter(User.delete == 0).all()
    parent_child_pair = (
        db.session.query(DepartmentOrg.parent_id, DepartmentOrg.child_id)
        .join(User, User.id == DepartmentOrg.child_id)
        .filter(User.delete == 0)
        .all()
    )

    id_name_pair = {
        id: [name, pos]
        for id, name, pos, no in db.session.query(
            User.id, User.name, User.position, User.no
        )
        .filter(User.delete == 0)
        .all()
    }

    # Build a directed graph and a list of all names that have no parent
    graph = {name: set() for tup in parent_child_pair for name in tup}
    has_parent = {name: False for tup in parent_child_pair for name in tup}
    for parent, child in parent_child_pair:
        graph[parent].add(child)
        has_parent[child] = True
    # All names that have absolutely no parent:
    roots = [name for name, parents in has_parent.items() if not parents]

    tree = traverse({}, graph, roots)

    orgchart = get_children([], tree[1], id_name_pair)[0]

    return render_template("assess/department_org.html", orgchart=orgchart)


def traverse(hierarchy, graph, names):
    for name in names:
        hierarchy[name] = traverse({}, graph, graph[name])
    return hierarchy


# 用于显示orgchart 颜色
# class 定义在html中
html_class_map = {
    "管道1组": "c1",
    "管道2组": "c2",
    "管道3组": "c3",
    "机械组": "c4",
    "销售报价组": "c5",
    "工艺组": "c6",
    "电仪组": "c7",
    "建造工艺组": "c8",
    "结构组": "c9",
    "数据信息组": "c10",
}


def get_children(children, tree, name_map):
    for k, v in tree.items():
        c = html_class_map.get(name_map[k][1].split()[0])
        if not c:
            c = ""
        children.append(
            {
                "id": k,
                "name": name_map[k][0],
                "title": name_map[k][1],
                "className": c,
                "children": get_children([], v, name_map),
            }
        )
    return children


if __name__ == "__main__":
    pass
